import os
import pandas as pd
import random
from openpyxl import Workbook, load_workbook
from openpyxl.chart import PieChart, Reference, Series
from openpyxl.utils.dataframe import dataframe_to_rows

# 配置参数
folder_path = r'd:\examq'  # 目标文件夹路径
question_count = 10  # 题目数量
answers_charset = ['A', 'B', 'C', 'D']  # 可选答案集合（统一为大写）
num_students = 44  # 学生人数
output_excel = 'student_data.xlsx'  # 输出文件名

# 创建基础文件结构
def initialize_files():
    if not os.path.exists(folder_path):
        os.makedirs(folder_path)

    # 生成标准答案文件（如果不存在）
    answer_path = os.path.join(folder_path, 'answer.txt')
    if not os.path.exists(answer_path):
        correct_answers = [random.choice(answers_charset) for _ in range(question_count)]
        with open(answer_path, 'w', encoding='utf-8') as f:
            f.write('\n'.join(correct_answers))
        print(f"自动生成答案文件：{answer_path}")

    # 生成学生答题文件
    for student_id in range(1, num_students + 1):
        file_name = f"{student_id:02d}学生{student_id}.txt"
        file_path = os.path.join(folder_path, file_name)

        content = []
        for q in range(1, question_count + 1):
            question = f"第{q}题: xxxx"
            answer = random.choice(answers_charset)  # 确保学生答案也是大写
            content.append(f"{question}\n{answer}\n")

        with open(file_path, 'w', encoding='utf-8') as f:
            f.write(''.join(content))
        print(f"已生成：{file_name}")

# 读取并整理学生数据
def parse_student_data():
    data = []
    for file_name in os.listdir(folder_path):
        if not file_name.endswith('.txt') or file_name == 'answer.txt':
            continue

        student_id = file_name[:2]
        file_path = os.path.join(folder_path, file_name)

        questions = []
        answers = []
        try:
            with open(file_path, 'r', encoding='utf-8') as f:
                lines = [line.strip() for line in f if line.strip()]

                valid = True
                for i in range(0, len(lines), 2):
                    if i + 1 >= len(lines):
                        valid = False
                        break
                    question_part = lines[i]
                    answer_part = lines[i + 1]

                    q_number = (i // 2) + 1
                    if not question_part.startswith(f"第{q_number}题: "):
                        valid = False
                        break
                    questions.append(question_part.split(': ', 1)[1].strip())
                    answers.append(answer_part)

                if valid and len(questions) == question_count:
                    for q_idx in range(question_count):
                        data.append({
                            '学号': student_id,
                            '题号': q_idx + 1,
                            '题目内容': questions[q_idx],
                            '学生答案': answers[q_idx]
                        })
        except Exception as e:
            print(f"❌ 处理文件 {file_name} 时发生错误：{e}")

    return data

# 主执行流程
initialize_files()
student_data = parse_student_data()

# 转换为DataFrame
df = pd.DataFrame(student_data)

# 读取正确答案文件
correct_answer_list = []
try:
    with open(os.path.join(folder_path, 'answer.txt'), 'r', encoding='utf-8') as f:
        correct_answer_list = [line.strip().upper() for line in f]  # 确保标准答案为大写
except FileNotFoundError:
    print("标准答案文件未找到！请确保已生成 'answer.txt' 文件。")
    exit(1)

# 计算学生分数和各题正确率
student_scores = {}
question_correct_count = [0] * question_count

# 按学号分组计算分数和正确率
for student_id, group in df.groupby('学号'):
    student_answers = group['学生答案'].tolist()
    score = sum(1 for ans, correct in zip(student_answers, correct_answer_list) if ans == correct) * 10
    student_scores[student_id] = score

    # 更新各题的正确答题人数
    for q_idx in range(question_count):
        student_ans = student_answers[q_idx]
        if student_ans == correct_answer_list[q_idx]:  # 比较时确保大小写一致
            question_correct_count[q_idx] += 1

# 计算正确率百分比
num_students_effective = len(student_scores)  # 实际参与的学生人数
if num_students_effective == 0:
    num_students_effective = 1  # 避免除以零
question_correct_rate = [count / num_students_effective for count in question_correct_count]
question_error_rate = [1 - rate for rate in question_correct_rate]

# 创建学生总得分表
score_summary = pd.DataFrame({
    '学号': list(student_scores.keys()),
    '总得分': list(student_scores.values())
})

# 创建正确率汇总表
rate_summary = pd.DataFrame({
    '题号': [f'第{q + 1}题' for q in range(question_count)],
    '正确率': [f'{rate * 100:.1f}%' for rate in question_correct_rate],
    '正确率数值': question_correct_rate,
    '错误率数值': question_error_rate
})

# 将结果保存到Excel的不同工作表并插入图片
output_path = os.path.join(folder_path, output_excel)
try:
    # 创建工作簿
    wb = Workbook()
    ws_details = wb.active
    ws_details.title = '学生答题详情'
    ws_scores = wb.create_sheet('学生得分汇总')
    ws_rates = wb.create_sheet('正确率汇总')

    # 保存学生答题详情
    for r in dataframe_to_rows(df, index=False, header=True):
        ws_details.append(r)

    # 保存学生得分汇总
    for r in dataframe_to_rows(score_summary, index=False, header=True):
        ws_scores.append(r)

    # 保存正确率汇总
    for r in dataframe_to_rows(rate_summary, index=False, header=True):
        ws_rates.append(r)

    

    wb.save(output_path)
    print(f"\n学生答题数据及统计信息已生成，文件保存在：{output_path}")
except Exception as e:
    print(f"❌ 保存Excel文件时发生错误：{e}")